package abstractGA.result;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import foundation.dbUtil.DBException;
import foundation.dbUtil.DBUtil;

/**
 * 请培凯认真对比你原来的代码，有不懂可记下来一起问我
 * 
 * @author nyc
 *
 */
public class AbsGADAO {

	protected String dbCfgFileName = "";
	protected DBUtil dbUtil = null;

	public AbsGADAO() {
		dbCfgFileName = "src/foundation/dbUtil/dbConf.properties";
		dbUtil = DBUtil.getInstance(dbCfgFileName);
	}

	public AbsGADAO(String dbCfgFileName) {
		this.dbCfgFileName = dbCfgFileName;
		dbUtil = DBUtil.getInstance(dbCfgFileName);
	}
	/**
	 * 
	 * @param oneOpti:将一次优化后的信息存入数据库中
	 * @return
	 */

	public synchronized boolean addAlgRun(OneOpt oneOpti) {
		boolean rlt = true;
		try {
			// 向主表T_Run中增加记录
			String sqlTxt = "insert into t_run(runID,genID,optID,runTime,Code,RestValue,ReltValue,costValue)";
			sqlTxt += "values(?,?,?,?,?,?,?,?)";// 共8个参数
			// 产生一个预编译的命令对象
			PreparedStatement pStmt = dbUtil.getPrepStmt(sqlTxt);
			// 获取参数值
			String runID = oneOpti.getRunID();// 算法运行标识
			String genID = oneOpti.getGenID();// 演化代数
			String optID = oneOpti.getOptID();// 优化次数
			String runTime = oneOpti.getRunTime();// 运行时间
			String code = oneOpti.getcode();// 个体编码
			float restValue = oneOpti.getRestValue();// 响应时间值
			float reltValue = oneOpti.getReltValue();// 可靠性值
			float costValue = oneOpti.getCostValue();// 可靠性值
			// 设定参数值
			pStmt.setString(1, runID);
			pStmt.setString(2, genID);
			pStmt.setString(3, optID);
			pStmt.setString(4, runTime);
			pStmt.setString(5, code);
			pStmt.setFloat(6, restValue);
			pStmt.setFloat(7, reltValue);
			pStmt.setFloat(8, costValue);
			
			//dbUtil.getConn().setAutoCommit(false);
			
			// 增加主记录
			pStmt.executeUpdate();
			pStmt.close();

			// 向子表T_Change中增加多条记录
			ArrayList<ChangeElem> chgElems = oneOpti.getChgElems();

			if (chgElems.size() > 0) {
				sqlTxt = "insert into t_change (runID,genID,optID,DoFID,elemName,value) ";
				sqlTxt += "values(?,?,?,?,?,?)";// 共6个参数
				pStmt = dbUtil.getPrepStmt(sqlTxt);// 构成批命令一次提交给数据库执行
				for (ChangeElem changeElem : chgElems) {
					// 获取参数值
					String DoFID = changeElem.getDoFID();// 自由度ID
					String elemName = changeElem.getElemName();// 变更元素的名称
					String value = changeElem.getValue();
					// 设定参数值
					pStmt.setString(1, runID);
					pStmt.setString(2, genID);
					pStmt.setString(3, optID);
					pStmt.setString(4, DoFID);
					pStmt.setString(5, elemName);
					pStmt.setString(6, value);
					// 增加批量参数
					//pStmt.addBatch();
					pStmt.executeUpdate();
				}				
				//int counts[] = pStmt.executeBatch();// 执行批命令
			/*	if (counts.length == 0) {// 更新数据库的记录等于0条
					rlt = false;
				}*/
			}
			
			//dbUtil.getConn().commit();
			//pStmt.clearBatch();
			//dbUtil.getConn().setAutoCommit(true);
			
			
			pStmt.close();
			//dbUtil.closeConn(); //避免频繁构造和销毁数据库连接
		} catch (SQLException e) {
			
			rlt = false;
			e.printStackTrace();

		} catch (DBException e) {
			rlt = false;
			e.printStackTrace();
		}

		return rlt;
	}

	/**
	 * 获取算法一次运行过程中每代种群的最佳个体（需要注意的是，我在数据库中新建了存储函数getChgElems，
	 * 故要更新一下并重新运行数据库生成脚步）， 这个方法相当于“期望的结果输出表.xls”中的名为“2”的表单
	 * @param runID:算法某次运行的标识
	 * @return
	 */
	public synchronized List<ChromOut> getBestChroms(String runID) {
		List<ChromOut> bestChromOuts = new ArrayList<ChromOut>();
		String sqlTxt = "SELECT t1.runID,t1.genID,t1.optID,t1.code,t1.RestValue, ";
		//getChgElems为数据库建好的存储函数，它将多条变更记录变化一个长字符串，详见数据库该函数的脚本
		sqlTxt += "t1.ReltValue,t1.costValue,getChgElems(t1.runID,t1.genID,t1.optID,5) chgedElems FROM ";
		sqlTxt += "(SELECT runID,genID,optID,t_run.code,RestValue,ReltValue,costValue FROM t_run ";
		sqlTxt += "WHERE runID =? ORDER BY RestValue ASC ) AS t1 ";
		sqlTxt += "GROUP BY t1.genID ORDER BY (t1.genID+0) asc";
         System.out.println(sqlTxt);
		
		// 产生一个预编译的命令对象
		try {
			PreparedStatement pStmt = dbUtil.getPrepStmt(sqlTxt);
			// 设定参数值
			pStmt.setString(1, runID);
			ResultSet rs = pStmt.executeQuery();
			while (rs.next()) {
				ChromOut bestChromOut = new ChromOut();
				bestChromOut.setGenID(rs.getString("genID"));
				bestChromOut.setCodes(rs.getString("code"));
				bestChromOut.setObjVal1(rs.getFloat("RestValue"));
				bestChromOut.setObjVal2(rs.getFloat("ReltValue"));
				bestChromOut.setObjVal3(rs.getFloat("costValue"));
				bestChromOut.setChgedElems(rs.getString("chgedElems"));
				bestChromOut.setBest(true);
				bestChromOuts.add(bestChromOut);
			}
			rs.close();
			pStmt.close();
		} catch (DBException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bestChromOuts;
	}

	public synchronized int execute(String sqlTxt) throws SQLException, DBException {
		PreparedStatement pStmt = dbUtil.getPrepStmt(sqlTxt);
		int rlt=pStmt.executeUpdate();
		pStmt.close();
		return rlt;
	}
	
	public synchronized List<List<ChromOut>> getPopuOut(String runID,int popuSize){
		/*
		 * List<ChormOut> 表示一代种群中的所有个体
		 * List<List<ChormOut>>表示一次运行，多代种群中的所有个体
		 */
		List<List<ChromOut>> multiChromOuts= new ArrayList<List<ChromOut>>();
		List<ChromOut> chromOuts = null;
		ChromOut chromOut =null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		//用一个List存放每一代的objval1最优标准
		List<Float> setOfBestbestObjVal1 = new ArrayList<Float>();
		
		String codes;
		String genID;
		float objVal1;
		float objVal2;
		float objVal3;
		float bestObjVal1 = Float.MAX_VALUE;
		
		String sqlTxt = "SELECT * FROM t_run where runID =  '" + runID + "' ORDER BY runTime ASC" ;
		try {
			pStmt = dbUtil.getPrepStmt(sqlTxt);
			rs = pStmt.executeQuery();
			//curse起到游标的作用
			int curse=0;
			//找到每一代的bestObjval1，并且将其保存		
			while (rs.next()) {
				if (bestObjVal1 > rs.getFloat("RestValue")) {
					bestObjVal1 = rs.getFloat("RestValue");
				}
				curse++;
				if(curse==popuSize){
					setOfBestbestObjVal1.add(bestObjVal1);
					bestObjVal1 = Float.MAX_VALUE;
					curse=0;
				}
			}
			//j起到控制循环的作用
			ResultSet rs2 = pStmt.executeQuery();
			//对每一个Chrom都进行格式化装配
			//根据runID的前几位判断是哪种算法，GA和nsga求装配最优解不一样
			if(runID.substring(0,2).equals("GA")||runID.substring(0,8).equals("rulSeqGA")){
				for(int j=0;j<setOfBestbestObjVal1.size();j++){
					chromOuts = new ArrayList<ChromOut>();
					
					for(int i=0;i<popuSize;i++){
						rs2.next();
						chromOut = new ChromOut();
						codes = rs2.getString("Code");
						chromOut.setCodes(codes);
						genID = rs2.getString("genID");
						chromOut.setGenID(genID);
						objVal1 = rs2.getFloat("RestValue");
						chromOut.setObjVal1(objVal1);
						objVal2 = rs2.getFloat("ReltValue");
						chromOut.setObjVal2(objVal2);
						objVal3 = rs2.getFloat("costValue");
						chromOut.setObjVal3(objVal3);
						
						if (objVal1 == setOfBestbestObjVal1.get(j)) {
							chromOut.setBest(true);
						} else {
							chromOut.setBest(false);
						}												
						chromOuts.add(chromOut);
					}
					multiChromOuts.add(chromOuts);
					
				}
			}else if(runID.substring(0,4).equals("NSGA")||
					runID.substring(0,10).equals("NSRulSeqGA")){
				for(int j=0;j<setOfBestbestObjVal1.size();j++){
					chromOuts = new ArrayList<ChromOut>();
					
					for(int i=0;i<popuSize;i++){
						rs2.next();
						chromOut = new ChromOut();
						codes = rs2.getString("Code");
						chromOut.setCodes(codes);
						genID = rs2.getString("genID");
						chromOut.setGenID(genID);
						objVal1 = rs2.getFloat("RestValue");
						chromOut.setObjVal1(objVal1);
						objVal2 = rs2.getFloat("ReltValue");
						chromOut.setObjVal2(objVal2);
						objVal3 = rs2.getFloat("costValue");
						chromOut.setObjVal3(objVal3);
						chromOut.setBest(false);	
						chromOuts.add(chromOut);
					}
					//此处对chromOuts再进行一次非支配排序，等级0的为最优解
					ArrayList<ArrayList<Integer>> idxss = nondominatedSort(chromOuts);
					ArrayList<Integer> idxs = idxss.get(0);
//					//只存储最优解集
//					List<ChromOut> bestChromOuts = new ArrayList<ChromOut>();
//					for(int i=0;i<chromOuts.size();i++){
//						bestChromOuts.add(null);
//					}
					for (Integer idx : idxs) {
						chromOuts.get(idx).setBest(true);
//						bestChromOuts.set(idx, chromOuts.get(idx));
					}
					multiChromOuts.add(chromOuts);	
//					multiChromOuts.add(bestChromOuts);	
				}
			}
			
		} catch (DBException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try{
			rs.close();
			pStmt.close();
			//dbUtil.closeConn();//避免频繁构造和销毁数据库连接
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return multiChromOuts;
	}
	
	public synchronized void  closeDBCon(){
		if(dbUtil!=null){
			try {
				dbUtil.closeConn();
			} catch (DBException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 非支配排序
	 * @param chromOuts：染色体集
	 * @return：按非支配等级排序后的染色体下标数组的数组，例如：
	 * 等级0：[2,3]（pareto最优染色体下标）
	 * 等级1：[0,1]
	 */
	public ArrayList<ArrayList<Integer>> nondominatedSort(List<ChromOut> chromOuts) {
		ArrayList<ArrayList<Integer>> idxss = new ArrayList<ArrayList<Integer>>();
		ArrayList<Integer> rnk0Idxs = new ArrayList<Integer>();// 等级0（最好）的染色体下标
		ArrayList<DominateInfo> dominateInfos = new ArrayList<DominateInfo>();
		int size = chromOuts.size();
		for (int i = 0; i < size; i++) {
			ChromOut chrom1 = chromOuts.get(i);
			DominateInfo dominateInfo = new DominateInfo();
			dominateInfo.idx = i;
			float resTime1 = (Float) chrom1.getObjVal1();		
			float reltVal1 = (Float) chrom1.getObjVal2();
			float costVal1 = (Float) chrom1.getObjVal3();	
			for (int j = 0; j < size; j++) {
				ChromOut chrom2 = chromOuts.get(j);
				if (chrom1 != chrom2) {
					float resTime2 = (Float) chrom2.getObjVal1();
					float reltVal2 = (Float) chrom2.getObjVal2();
					float costVal2 = (Float) chrom2.getObjVal3();
	
					if ((Float.compare(resTime2, resTime1) <= 0 &&  (Float.compare(costVal2, costVal1) < 0)&&(Float.compare(reltVal2, reltVal1) >= 0))// chrom2比chrom1优
							|| (Float.compare(resTime2, resTime1) <= 0 && (Float.compare(costVal2, costVal1) <= 0)&&(Float.compare(reltVal2, reltVal1) > 0))
							|| (Float.compare(resTime2, resTime1) < 0 && (Float.compare(costVal2, costVal1) <= 0)&&(Float.compare(reltVal2, reltVal1) >= 0))) {
						dominateInfo.superiorNum += 1;
					}

					if ((Float.compare(resTime1, resTime2) <= 0 && (Float.compare(costVal1, costVal2) < 0)&&(Float.compare(reltVal1, reltVal2) >= 0))// chrom2比chrom1劣
							|| (Float.compare(resTime1, resTime2) <= 0 && (Float.compare(costVal1, costVal2) <= 0)&&(Float.compare(reltVal1, reltVal2) > 0))
							|| (Float.compare(resTime1, resTime2) < 0 && (Float.compare(costVal1, costVal2) <= 0)&&(Float.compare(reltVal1, reltVal2) >= 0))) {
						dominateInfo.inferiorIdxs.add(new Integer(j));
					}

				}
			}
			if (dominateInfo.superiorNum == 0) {
				rnk0Idxs.add(i);
			}
			dominateInfos.add(dominateInfo);
		}
		if (rnk0Idxs.size() > 0) {
			idxss.add(rnk0Idxs);
		}

		ArrayList<Integer> curRnkIdxs = rnk0Idxs;
		while (curRnkIdxs.size() > 0) {
			ArrayList<Integer> nxtRnkIdxs = new ArrayList<Integer>();
			for (Integer idx : curRnkIdxs) {
				DominateInfo curInfo = dominateInfos.get(idx);
				for (Integer inferiorIdx : curInfo.inferiorIdxs) {
					DominateInfo inferiorInfo = dominateInfos.get(inferiorIdx);
					inferiorInfo.superiorNum -= 1;
					if (inferiorInfo.superiorNum == 0) {
						nxtRnkIdxs.add(inferiorInfo.idx);
					}
				}
			}
			if (nxtRnkIdxs.size() > 0) {
				idxss.add(nxtRnkIdxs);
			}
			curRnkIdxs = nxtRnkIdxs;
		}
		return idxss;
	}
	
	/**
	 * 支配信息
	 * @author nyc
	 *
	 */
	protected class DominateInfo {
		int idx = -1;// 本个体在种群中的下标
		int superiorNum = 0;// 比本个体优的个体数目
		List<Integer> inferiorIdxs = new ArrayList<Integer>();// 比本个体差的个体的下标

	}
}
